package com.han.dao.concrete.product;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.han.bom.Exam;
import com.han.bom.PracticeExam;
import com.han.bom.Professor;
import com.han.bom.RealExam;
import com.han.bom.Student;
import com.han.bom.User;
import com.han.dao.abstraction.product.GenericUserDAO;

public class UserDAO extends DataAccessObject implements GenericUserDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = 6497143581973806787L;

	public User retrieveUserById(String userID) throws ClassNotFoundException,
			SQLException, NamingException {
		User user = null;
		String id = userID.substring(1);
		int id2 = Integer.parseInt(id);
		Boolean type = null;

		try {
			this.connect();
			String select = "SELECT * FROM tbl_user WHERE user_id = ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id2);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean(6);

				if (type) {
					user = new Professor(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				} else {
					user = new Student(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return user;
	}

	public User retrieveUserByUsername(String username)
			throws ClassNotFoundException, SQLException, NamingException {
		User user = null;
		Boolean type = null;

		try {
			this.connect();
			String select = "SELECT * FROM tbl_user WHERE user_username = ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setString(1, username);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean(6);

				if (type) {
					user = new Professor(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				} else {
					user = new Student(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return user;
	}

	public ArrayList<Student> retrieveStudentsByProfessor(String profID)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int id = Integer.parseInt(profID.substring(1));

		try {
			this.connect();
			String select = "SELECT * FROM tbl_user AS a INNER JOIN tbl_professor_student AS b ON a.user_id = b.student_id WHERE b.professor_id = ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));

				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return students;
	}

	public ArrayList<Student> retrieveStudentsIncludedInExam(String examid)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int eid = 0;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM tbl_user AS a INNER JOIN tbl_student_exam AS b ON a.user_id = b.student_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));
				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return students;
	}

	public ArrayList<Student> retrieveStudentsByExamTaken(String examID)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int eid = 0;

		try {
			eid = Integer.parseInt(examID.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT a.* FROM tbl_user AS a INNER JOIN tbl_exam_response AS b ON a.user_id=b.student_id LEFT OUTER JOIN tbl_questions_answered AS c ON b.exam_response_id=c.exam_response_id WHERE b.exam_id=? and b.istaken=true and b.submitted=true";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));

				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return students;
	}

	public ArrayList<Student> retrieveStudentsTakingExam(String examID)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		ExamDAO examdao = new ExamDAO();
		Exam exam = null;
		Student student = null;
		String key = "";

		try {
			this.connect();
			exam = examdao.retrieveExamById(examID);
			key = examID
					+ "%T"
					+ ((exam instanceof RealExam) ? ((RealExam) exam)
							.getTimesActivated() : ((PracticeExam) exam)
							.getTimesTaken());
			String select = "SELECT DISTINCT a.* FROM tbl_user AS a INNER JOIN tbl_exam_response AS b ON a.user_id=b.student_id LEFT OUTER JOIN tbl_questions_answered AS c ON b.exam_response_id=c.exam_response_id WHERE b.exam_id=? and b.istaken=true and b.submitted=false";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setString(1, key);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));

				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return students;
	}

	public ArrayList<Student> retrieveStudentsByExamMissed(String examID)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int eid = 0;

		try {
			eid = Integer.parseInt(examID.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT a.* FROM tbl_user AS a INNER JOIN tbl_exam_response AS b ON a.user_id=b.student_id WHERE b.exam_id=? AND b.istaken=false and b.submitted=false";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));

				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return students;
	}

	public ArrayList<Student> retrieveStudentsByKeyword(String key,
			String profID) throws ClassNotFoundException, SQLException,
			NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int id = Integer.parseInt(profID.substring(1));
		int stid = 0;
		String intkey = "%" + key + "%";

		try {
			if (!key.contains("E") && !key.contains("Q")) {
				stid = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			stid = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT * FROM tbl_user AS a INNER JOIN tbl_professor_student AS b ON a.user_id = b.student_id WHERE b.professor_id = ? AND (a.user_id = ? OR UPPER(a.user_first_name) LIKE UPPER(?) OR UPPER(a.user_last_name) LIKE UPPER(?) OR UPPER(a.user_username) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, stid);
			this.preparedStatement.setString(3, intkey);
			this.preparedStatement.setString(4, intkey);
			this.preparedStatement.setString(5, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));

				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return students;
	}

	public int retrieveMaxUserID() throws ClassNotFoundException, SQLException,
			NamingException {
		int id = 0;

		try {
			this.connect();
			String select = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'beta_test_engine' AND TABLE_NAME = 'tbl_user'";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				id = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return id;
	}

	public boolean addStudentToProfessor(String studentId, String profId)
			throws SQLException, ClassNotFoundException, NamingException {
		boolean success = false;
		String insert = "INSERT IGNORE INTO tbl_professor_student(professor_id,student_id) VALUES((SELECT user_id FROM tbl_user WHERE user_id=?),(SELECT user_id FROM tbl_user WHERE user_id=?))";
		int profid = 0;
		int studentid = 0;

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			try {
				profid = Integer.parseInt(profId.substring(1));
			} catch (NumberFormatException e) {
				profid = 0;
			}

			try {
				studentid = Integer.parseInt(studentId.substring(1));
			} catch (NumberFormatException e) {
				studentid = 0;
			}

			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setInt(1, profid);
			this.preparedStatement.setInt(2, studentid);
			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();

			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean insertUser(User user) throws SQLException,
			ClassNotFoundException, NamingException {
		boolean success = false;
		String insert = "INSERT INTO tbl_user(user_first_name,user_last_name,user_username,user_password,user_status,user_active) VALUES(?,?,?,?,?,?)";

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, user.getFirstName());
			this.preparedStatement.setString(2, user.getLastName());
			this.preparedStatement.setString(3, user.getUsername());
			this.preparedStatement.setString(4, user.getPassword());

			if (user instanceof Professor) {
				this.preparedStatement.setBoolean(5, true);
			} else {
				this.preparedStatement.setBoolean(5, false);
			}

			this.preparedStatement.setBoolean(6, true);
			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean updateUser(User user) throws SQLException,
			ClassNotFoundException, NamingException {
		boolean success = false;
		String insert = "UPDATE tbl_user SET user_first_name=?,user_last_name=?,user_username=?,user_password=?,user_status=?,user_active=? WHERE user_id=?";
		int userid = 0;

		try {
			userid = Integer.parseInt(user.getUserID().substring(1));
		} catch (NumberFormatException e) {
			userid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, user.getFirstName());
			this.preparedStatement.setString(2, user.getLastName());
			this.preparedStatement.setString(3, user.getUsername());
			this.preparedStatement.setString(4, user.getPassword());

			if (user instanceof Professor) {
				this.preparedStatement.setBoolean(5, true);
			} else {
				this.preparedStatement.setBoolean(5, false);
			}

			this.preparedStatement.setBoolean(6, true);
			this.preparedStatement.setInt(7, userid);

			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean deleteUser(String userId) throws SQLException,
			ClassNotFoundException, NamingException {
		boolean success = false;
		String insert = "DELETE FROM tbl_user WHERE user_id=?";
		int id = 0;

		try {
			id = Integer.parseInt(userId.substring(1));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, id);
			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}
}
